
require 'databaseInterface'

class ServerDAO
  
  @@instance = ServerDAO.new
  
  def self.instance
      return @@instance
  end
  
  #Accesses the database and sends a query asking for everything from table TABLE with key KEY
  def databaseGet(table,key)#TODO:Change the query // test method
    conn = nil
    response = []
    
    con = DatabaseInterface.instance.getDbConnection("GroupDB")
    statement = con.createStatement()
    dbresp = statement.executeQuery("select NAME,POSITION from #{table} where NAME='#{key}'")
    
    while(dbresp.next())
      response.push(dbresp.getString("NAME") + " - > " + dbresp.getString("POSITION") )
    end
    
    dbresp.close()
    statement.close()
    DatabaseInterface.instance.closeConnection(con) unless con.nil?
    

    return response
  end
  
  def fetchCandidateNames()
    conn = nil
    response = []
    con = DatabaseInterface.instance.getDbConnection("VotingDB")
    statement = con.createStatement()
    dbresp = statement.executeQuery("select candidate from votingtables union select candidate from votingtables")
    while(dbresp.next())
      response.push(dbresp.getString("CANDIDATE"))
    end
    dbresp.close()
    statement.close()
    DatabaseInterface.instance.closeConnection(con) unless con.nil?
    return response
  end
  
  def fetchDepartmentNames()
    conn = nil
    response = []
    con = DatabaseInterface.instance.getDbConnection("VotingDB")
    statement = con.createStatement()
    dbresp = statement.executeQuery("select dptname from departments union select dptname from departments")
    while(dbresp.next())
      response.push(dbresp.getString("DPTNAME"))
    end
    dbresp.close()
    statement.close()
    DatabaseInterface.instance.closeConnection(con) unless con.nil?
    return response
  end
  
  def fetchTotalVoles()
     conn = nil
     response = 0
     con = DatabaseInterface.instance.getDbConnection("VotingDB")
     statement = con.createStatement()
     dbresp = statement.executeQuery("select sum (totalvotes) from votingtables")
     if(dbresp.next())
       response  = dbresp.getInt(1) 
     end
     dbresp.close()
     statement.close()
     DatabaseInterface.instance.closeConnection(con) unless con.nil?
     return response
  end
  
  def fetchCandidateVotes(candidateName)
     conn = nil
     response = 0
     con = DatabaseInterface.instance.getDbConnection("VotingDB")
     statement = con.createStatement()
     dbresp = statement.executeQuery("select sum (totalvotes) from votingtables where candidate = '#{candidateName}'")
     if(dbresp.next())
       response  = dbresp.getInt(1) 
     end
     dbresp.close()
     statement.close()
     DatabaseInterface.instance.closeConnection(con) unless con.nil?
     return response
  end
  
  def databaseTableInsert(tableKey,tableNum,cand1,cand2,cand3,vt1,vt2,vt3)
     conn = nil
     response = 0
     con = DatabaseInterface.instance.getDbConnection("VotingDB")
     statement = con.createStatement()
     statement.executeUpdate("insert into votingtables values('#{tableKey}',#{tableNum},'#{cand1}',#{vt1})")
     statement.close()
     #---
     statement = con.createStatement()
     statement.executeUpdate("insert into votingtables values('#{tableKey}',#{tableNum},'#{cand2}',#{vt2})")
     
     statement.close()
     #---
     statement = con.createStatement()
     statement.executeUpdate("insert into votingtables values('#{tableKey}',#{tableNum},'#{cand3}',#{vt3})")
     
     statement.close() 
     #-----
     DatabaseInterface.instance.closeConnection(con) unless con.nil?
     puts "Inserted Succesfully!"
     return response
  end
  
  def dbTableDel(tableKey)
     conn = nil
     response = 0
     con = DatabaseInterface.instance.getDbConnection("VotingDB")
     statement = con.createStatement()
     statement.executeUpdate("delete from votingtables where keyvotingtable = '#{tableKey}'")
     statement.close()
     #---
     DatabaseInterface.instance.closeConnection(con) unless con.nil?
     puts "Deleted Succesfully!"
     return response
  end 
  
  def dbtableUpdate(tableKey,tableNum,cand1,cand2,cand3,vt1,vt2,vt3)
     conn = nil
     response = 0
     con = DatabaseInterface.instance.getDbConnection("VotingDB")
     statement = con.createStatement()
     statement.executeUpdate("update votingtables set keyvotingtable = '#{tableKey}', tableid = #{tableNum}, candidate = '#{cand1}', totalvotes = #{vt1}
    where keyvotingtable = '#{tableKey}' and tableid = #{tableNum} and candidate =  '#{cand1}' ")
     statement.close()
     #---
     statement = con.createStatement()
     statement.executeUpdate("update votingtables set keyvotingtable = '#{tableKey}', tableid = #{tableNum}, candidate = '#{cand2}', totalvotes = #{vt2}
    where keyvotingtable = '#{tableKey}' and tableid = #{tableNum} and candidate =  '#{cand2}' ")
     
     statement.close()
     #---
     statement = con.createStatement()
     statement.executeUpdate("update votingtables set keyvotingtable = '#{tableKey}', tableid = #{tableNum}, candidate = '#{cand3}', totalvotes = #{vt3}
    where keyvotingtable = '#{tableKey}' and tableid = #{tableNum} and candidate =  '#{cand3}' ")
     
     statement.close() 
     #-----
     DatabaseInterface.instance.closeConnection(con) unless con.nil?
     puts "Updated Succesfully!"
     return response
  end
   

  private_class_method :new
end
